/**
 * Copyright (c) 2019 Coder League
 * All rights reserved.
 *
 * File：MembersDaoImpl.java
 * History:
 *         2019年5月27日: Initially created, CJH.
 */
package club.coderleague.ilsp.dao.impl;

import java.util.Arrays;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import club.coderleague.data.jpa.domain.Page;
import club.coderleague.data.jpa.id.support.SnowflakeV4;
import club.coderleague.data.jpa.repository.support.AbstractDataRepositoryExtension;
import club.coderleague.ilsp.common.domain.beans.MobileUser;
import club.coderleague.ilsp.common.domain.beans.UserSession;
import club.coderleague.ilsp.common.domain.enums.DiamondRecordType;
import club.coderleague.ilsp.common.domain.enums.EntityState;
import club.coderleague.ilsp.common.domain.enums.IntegralRecordType;
import club.coderleague.ilsp.common.domain.enums.MemberBindType;
import club.coderleague.ilsp.common.domain.enums.OrderDeliveryState;
import club.coderleague.ilsp.common.domain.enums.OrderPaymentState;
import club.coderleague.ilsp.common.domain.enums.OrderType;
import club.coderleague.ilsp.common.domain.enums.UserGroup;
import club.coderleague.ilsp.common.util.CommonUtil;
import club.coderleague.ilsp.dao.extension.MembersDaoExtension;
import club.coderleague.ilsp.entities.Members;

/**
 * 会员DaoImpl
 * 
 * @author CJH
 */
public class MembersDaoImpl extends AbstractDataRepositoryExtension<Members, Long, SnowflakeV4> implements MembersDaoExtension {

	/**
	 * @see club.coderleague.ilsp.dao.extension.MembersDaoExtension#findExistsExpireDiamond()
	 */
	@Override
	public List<Members> findExistsExpireDiamond() {
		String jpql = "from Members m where exists(select 1 from Diamondrecords dr where dr.memberid = m.entityid and dr.entitystate = ?0"
			+ " and dr.recordtype = ?1 and (dr.remaineddiamond - dr.lockeddiamond) > 0 and"
			+ " datediff(now(), dr.recordtime) > (select sc.diamondtimeout from Systemconfigs sc where sc.entitystate = ?0))";
		return super.queryEntityList(jpql, EntityState.VALID.getValue(), DiamondRecordType.INCOME.getValue());
	}

	/**
	 * @see club.coderleague.ilsp.dao.extension.MembersDaoExtension#findExistsExpireIntegral()
	 */
	@Override
	public List<Members> findExistsExpireIntegral() {
		String jpql = "from Members m where exists(select 1 from Integralrecords ir where ir.memberid = m.entityid and ir.entitystate = ?0"
			+ " and ir.recordtype = ?1 and (ir.remainedintegral - ir.lockedintegral) > 0 and "
			+ " (year(now()) - year(ir.recordtime)) > (select sc.integraltimeout from Systemconfigs sc where sc.entitystate = ?0))";
		return super.queryEntityList(jpql, EntityState.VALID.getValue(), IntegralRecordType.INCOME.getValue());
	}
	
	/**
	 * @see club.coderleague.ilsp.dao.extension.MembersDaoExtension#queryUserSession(club.coderleague.ilsp.common.domain.enums.MemberBindType, java.lang.String)
	 */
	@Override
	public UserSession queryUserSession(MemberBindType bindType, String bindId) {
		String sql = "select m.entityid as userid, m.membertype, m.nickname, mb.bindid as openid from members m, memberbinds mb " 
			+ "where m.entityid = mb.memberid and m.entitystate = ?0 and mb.entitystate = ?1 and mb.bindtype = ?2 and mb.bindid = ?3";
		
		UserSession us = super.queryCustomBeanBySql(UserSession.class, sql, EntityState.VALID.getValue(), EntityState.VALID.getValue(), bindType.getValue(), bindId);
		if (us != null) us.setUsergroup(UserGroup.MEMBER.getValue());
		
		return us;
	}
	
	/**
	 * @see club.coderleague.ilsp.dao.extension.MembersDaoExtension#updateLoginTime(long)
	 */
	@Override
	public void updateLoginTime(long member) {
		String sql = "update members set lastlogin = ?0 where entityid = ?1";
		super.executeUpdateBySql(sql, new Date(), member);
	}
	
	/**
	 * @see club.coderleague.ilsp.dao.extension.MembersDaoExtension#queryBoundPhone(club.coderleague.ilsp.common.domain.enums.MemberBindType, java.lang.String)
	 */
	@Override
	public String queryBoundPhone(MemberBindType bindType, String bindId) {
		String sql = "select m.phonehash from members m, memberbinds mb where m.entityid = mb.memberid and mb.entitystate = ?0 and mb.bindtype = ?1 and mb.bindid = ?2";
		return super.queryStringBySql(sql, EntityState.VALID.getValue(), bindType.getValue(), bindId);
	}
	
	@Override
	public Page<Members> getTheAllMembers(Integer pageIndex, Integer pageSize, String key) {
		Map<String,Object> map = new HashMap<String, Object>();
		StringBuffer qsql = new StringBuffer(" select * from Members mb where mb.EntityState = :istate ");
		StringBuffer csql = new StringBuffer(" select count(mb.entityid) from Members mb where mb.EntityState = :istate ");
		map.put("istate", EntityState.VALID.getValue());
		if(!CommonUtil.isEmpty(key)) {
			qsql.append("");
			csql.append("");
			map.put("key", "%"+key+"%");
		}
		return super.queryEntityPageBySql(csql.toString(), qsql.toString(), pageIndex, pageSize, map);
	}
	
	/**
	 * @see club.coderleague.ilsp.dao.extension.MembersDaoExtension#queryAvailableDiamond(long)
	 */
	@Override
	public Integer queryAvailableDiamond(long member) {
		String sql = "select f_get_valid_diamond(?0)";
		Long result = super.queryLongBySql(sql, member);
		return result.intValue();
	}

	@Override
	public MobileUser getTheMobileUserInfo(Long entityid) {
		String sql = " select EntityId as memberid, Nickname as nickname, MemberPhone as memberphone from Members where entityid = ?0 ";
		return super.queryCustomBeanBySql(MobileUser.class, sql, entityid);
	}

	/**
	 * @see club.coderleague.ilsp.dao.extension.MembersDaoExtension#getMemberDiamondByMemberIdDao(java.lang.Long)
	 */
	@Override
	public Long getMemberDiamondByMemberIdDao(Long memberid) {
		String sql = "select f_get_valid_diamond (?0)";
		return super.queryLongBySql(sql, memberid);
	}

	/**
	 * @see club.coderleague.ilsp.dao.extension.MembersDaoExtension#getMemberIntegralByMemberIdDao(java.lang.Long)
	 */
	@Override
	public Long getMemberIntegralByMemberIdDao(Long memberid) {
		String sql = "select f_get_valid_integral (?0)";
		return super.queryLongBySql(sql, memberid);
	}

	/**
	 * @see club.coderleague.ilsp.dao.extension.MembersDaoExtension#getObligationByMemberIdDao(java.lang.Long)
	 */
	@Override
	public Long getObligationByMemberIdDao(Long memberid) {
		String sql = "select count(entityid) from Orders where entitystate = ?0 and paymentstate = ?1 and memberid = ?2 and OrderType IN (?3) ";
		return super.queryLongBySql(sql, EntityState.CONFIRMED.getValue(), OrderPaymentState.UNPAID.getValue(), memberid, Arrays.asList(new Integer[] {OrderType.NORMAL.getValue(),OrderType.ACTIVITY.getValue()}));
	}

	/**
	 * @see club.coderleague.ilsp.dao.extension.MembersDaoExtension#getWaitReceivByMemberIdDao(java.lang.Long)
	 */
	@Override
	public Long getWaitReceivByMemberIdDao(Long memberid) {
		String sql = "select count(entityid) from Orders where entitystate = ?0 and paymentstate = ?1 and deliverystate = ?2 and memberid = ?3 and OrderType IN (?4) ";
		return super.queryLongBySql(sql, EntityState.CONFIRMED.getValue(), OrderPaymentState.PAID.getValue(), OrderDeliveryState.SHIPPED.getValue(), memberid, Arrays.asList(new Integer[] {OrderType.NORMAL.getValue(),OrderType.ACTIVITY.getValue()}));
	}
}
